Does Sqlite have the concept of a schema in naming tables/views?

Question

From everything I've read and searched on I think the answer is no because it's never mentioned. But I never saw an explicit no.

Does Sqlite allow for a schema like in Sql Server you can have dbo. Employee.FirstName? Or is it limited to Employee.FirstName?

thanks - dave

Answer 1

I don't think sqlite has the exact same concept as schemas have in some other databases but you can attach several databases and operate on them by name. e.g.

$ sqlite3 mydb1
SQLite version 3.20.1 2017-08-24 16:21:36
sqlite> create table test1 (id int);  insert into test1 values (1);
sqlite> .tables
test1

Above we just have 1 database, which is the default database and doesn't need to be prefixed by the name of the database. However the name of this default default database is main , so we can do:

sqlite> select * from test1;
1
sqlite> select * from main.test1;
1

We can attach another database, which will be available under the name myotherdb.

sqlite> attach database 'myotherdb' as 'myotherdb';
sqlite> create table myotherdb.test1 (id int);  insert into myotherdb.test1 values (2);

Now we can use myotherdb to refer to this new database, and no prefix or the main prefix to refer to the first/default database

sqlite> select * from myotherdb.test1 where myotherdb.test1.id > 0;
2
sqlite> select * from test1 where test1.id > 0;
1
sqlite> select * from main.test1 where main.test1.id > 0;
1

Note that this will create 2 different database files

sqlite> .databases
main: /tmp/mydb1
myotherdb: /tmp/myotherdb

These 2 databases files can be opened individually later on.