How can I connect to a local sqlite3 database?

Question

I'm running a local node.js app on localhost:3000. It has a database named database in a file called db.sqlite. I tried to connect to the database with the host localhost:3000, but sequel pro says MySQL said: Unknown MySQL server host 'localhost:3000' (0)

Answer 1

db.sqlite is probably an SQLite database, not a MySQL database. Sequel Pro can only connect to MySQL databases.

For SQLite, you can try SQLite Database Browser, or download SQLite 3. Note that Mac is shipped with sqlite3.

Command Line SQLite

Using the sqlite3 command line, you can open a database file

$ sqlite3 db.sqlite

List out tables and the schema

sqlite> .schema customers
CREATE TABLE "customers"
(
    [CustomerId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    [FirstName] NVARCHAR(40)  NOT NULL,
    [LastName] NVARCHAR(20)  NOT NULL,
    [Company] NVARCHAR(80),
    [Address] NVARCHAR(70),
    [City] NVARCHAR(40),
    [State] NVARCHAR(40),
    [Country] NVARCHAR(40),
    [PostalCode] NVARCHAR(10),
    [Phone] NVARCHAR(24),
    [Fax] NVARCHAR(24),
    [Email] NVARCHAR(60)  NOT NULL,
    [SupportRepId] INTEGER,
    FOREIGN KEY ([SupportRepId]) REFERENCES "employees" ([EmployeeId]) 
        ON DELETE NO ACTION ON UPDATE NO ACTION
);
CREATE INDEX [IFK_CustomerSupportRepId] ON "customers" ([SupportRepId]);
sqlite> .tables
albums          employees       invoices        playlists     
artists         genres          media_types     tracks        
customers       invoice_items   playlist_track

And execute SQL statement

sqlite> SELECT CustomerId, FirstName, Country from customers LIMIT 5;
1|Luís|Brazil
2|Leonie|Germany
3|François|Canada
4|Bjørn|Norway
5|František|Czech Republic

The example above is run using the Chinook database.